package user.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import user.db.DBConnection;
import user.model.Book;
import user.model.BookAdmin;

public class BookAdminDao {
	
		/**
		 * 图书管理员登录验证
		 * @param con
		 * @param adminMessage
		 * @return
		 * @throws Exception
		 */
		public BookAdmin bookAdminLogin(Connection con, BookAdmin adminMessage) throws Exception {
		
			BookAdmin resultAdmin = null;
			String sql = "select * from bookAdmin where bookAdminName = ? and password = ?";
			PreparedStatement pstmt = con.prepareStatement(sql);
			pstmt.setString(1,adminMessage.getAdminName());
			pstmt.setString(2,adminMessage.getPassword());
			ResultSet rs = pstmt.executeQuery();
			
	 		if(rs.next()) {
				resultAdmin = new BookAdmin();
			    resultAdmin.setAdminId(rs.getString("bookAdminId"));
				resultAdmin.setAdminName(rs.getString("bookAdminName"));
				resultAdmin.setPassword(rs.getString("password"));
			}	
	 		
			return resultAdmin;
		}
		
		/**
		 * 实现添加图书功能
		 * @param book
		 * @return
		 * @throws Exception
		 */
		public int addBook(Book book) throws Exception {
			Connection conn = DBConnection.getConnection();
			//先在数据库中查询主键id，若查到了，返回0，代表注册失败
			String sqlid = "select * from book where id = ?";
			PreparedStatement pstmtid = conn.prepareStatement(sqlid);
			pstmtid.setInt(1, book.getBookId());
			ResultSet rsid = pstmtid.executeQuery();
			if(rsid.next()){
				return 0;
			}
			//若数据库中没有id，则可以插入
			String sql = "insert into book values(?,?,?,?,?,?)";
			PreparedStatement ps = null;
			try {
				ps = conn.prepareStatement(sql);
				ps.setInt(1, book.getBookId());
				ps.setString(2, book.getBookName());
				ps.setString(3, book.getAuthor());
				ps.setString(4, book.getType());
				ps.setFloat(5, book.getPrice());
				ps.setInt(6,1);
			} catch (Exception e) {
				e.printStackTrace();
			}
			return ps.executeUpdate();
		}
		
		public int deleteBook(int bookid) throws Exception {
			Connection con = DBConnection.getConnection();
			String sql = "delete from book where id = ?";
			PreparedStatement ps = null;
			ResultSet rs = null;
			try {
				ps = con.prepareStatement(sql);
				ps.setInt(1,bookid);
			}catch (Exception e) {
				e.printStackTrace();
			}
			return ps.executeUpdate();
		}
		
		public int updateBook(Book book) throws Exception {
			Connection conn = DBConnection.getConnection();
			String sql = "update book set name = ?,author = ?,type = ?,price = ? where id = ?";
			PreparedStatement ps = null;
			ResultSet rs = null;
			try {
				ps = conn.prepareStatement(sql);
				ps.setString(1, book.getBookName());
				ps.setString(2, book.getAuthor());
				ps.setString(3, book.getType());
				ps.setFloat(4, book.getPrice());
				ps.setInt(5, book.getBookId());
				}
				catch (Exception e) {
				e.printStackTrace();
				}
			return ps.executeUpdate();
		}
		
		public List<Book> listBook(String bookName,String author){
			String sql = "select * from book where name like ? and author like ? ";
			PreparedStatement ps = null;
			ResultSet rs = null;
			Connection connection = DBConnection.getConnection();
			List<Book> list = new ArrayList<Book>();
			try {
				ps = connection.prepareStatement(sql);
				ps.setString(1, "%"+bookName+"%");
				ps.setString(2, "%"+author+"%");
				rs = ps.executeQuery();
				while(rs.next()) {
					int id = rs.getInt("id");
					String name = rs.getString("name");
					String bookAuthor = rs.getString("author");
					String type = rs.getString("type");
					float price = rs.getFloat("price");
					int lend = rs.getInt("lend");
					Book book = new Book(id,name,bookAuthor,type,price,lend);
					list.add(book);
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
			return list;
		}

		/**
		 * 修改密码
		 * @param newpwd1
		 * @param loginName
		 * @return
		 */
		public int updatePwd(String newpwd1, String loginName) {
			Connection con = DBConnection.getConnection();
			PreparedStatement ps = null;
			ResultSet rs = null;
			String sql="update bookadmin set password=? where bookadminname=?";
			int i = 0;
			try {
				ps = con.prepareStatement(sql);
				ps.setString(1, newpwd1);
				ps.setString(2, loginName);
				i = ps.executeUpdate();
			} catch (Exception e) {
				e.printStackTrace();
			}finally {
				DBConnection.close(rs, ps);
			}
			return i;
		}
		
		
}
